IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IE_SvcEntityStaffedPerson_Cleanup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[IE_SvcEntityStaffedPerson_Cleanup]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.IE_SvcEntityStaffedPerson_Cleanup  

AS
BEGIN
	SET NOCOUNT ON;

	SELECT DISTINCT MAP.Id	
	INTO #tempIDs
	FROM SvcEntity_AdmStaffedPerson_Map MAP JOIN Stg_IE_SvcEntityStaffedPerson_Ctl
	ON MAP.SvcEntityId =  Stg_IE_SvcEntityStaffedPerson_Ctl.SvcEntityId
	WHERE NOT EXISTS (SELECT * FROM Stg_IE_SvcEntityStaffedPerson_Ctl STG
	WHERE STG.SvcEntityStaffedPersonMapId = MAP.Id)

	DELETE AgrStaffedPersonEffortYear
	WHERE SvcEntityStaffedPersonMapId IN (SELECT Id FROM #tempIDs) 

	DELETE SvcEntity_AdmStaffedPerson_Map
	WHERE Id IN (SELECT Id FROM #tempIDs) 

	DELETE PEY
	FROM AgrStaffedPersonEffortYear PEY JOIN Stg_IE_SvcEntityStaffedPerson_Ctl STG
		ON STG.SvcEntityStaffedPersonMapId = PEY.SvcEntityStaffedPersonMapId
	WHERE NOT EXISTS (SELECT PEY.Id FROM Stg_IE_SvcEntityStaffedPerson_Ctl STG
	WHERE STG.SvcEntityStaffedPersonMapId = PEY.SvcEntityStaffedPersonMapId
	AND STG.AgrBudgetViewTypeId = PEY.AgrBudgetViewTypeId
	AND STG.nYear = PEY.SequenceYear) 

	
END
GO
